﻿if object_id('fn_MapHTML_GpsVisitDistributionPole') is not null
DROP FUNCTION fn_MapHTML_GpsVisitDistributionPole

GO

CREATE FUNCTION [dbo].[fn_MapHTML_GpsVisitDistributionPole]
(
	@InspectionVisitPoleId int
)
RETURNS varchar(max)
AS
BEGIN
	declare @img xml
	set @img = '<img src="http://dev-poleposition.com/DEV/Icons/transparent_150.png" />'

	declare @html nvarchar(max)
	set @html = 
	(
		select 
			 'Distribution GPS Visit' as 'TITLE'
			,Pole.*
			,maps.Name as 'MapName'
			,sections.Name  as 'SectionName'
			,species.Name as 'SpecieName'
			,classes.Name as 'ClassName'
			,lengths.Name as 'LengthName'
			,treatments.Name as 'TreatMentName'
			
			-- visit images <IMAGE href="" src="" />

			,(select
				 replace(replace(replace(replace(Src.SettingValue, '{0}', Images.tb_Vendor_Distribution_Images_ImageId), '{1}', 'GPSPoleDistribution'), '{2}', 85), '{3}', 85) as '@src'
				--,replace(replace(replace(replace(Href.SettingValue, '{0}', Images.tb_Vendor_Distribution_Images_ImageId), '{1}', 'GPSPoleDistribution'), '{2}', 640), '{3}', 480) as '@href'
				,replace(Href.SettingValue, '{0}', replace(replace(replace(replace(Src.SettingValue, '{0}', Images.tb_Vendor_Distribution_Images_ImageId), '{1}', 'GPSPoleDistribution'), '{2}', 640), '{3}', 480)) as '@href'
				,Images.*
				from 
					tb_Vendor_Distribution_Images as Images
				where 
					Images.Vendor_Distribution_PoleFk = Pole.Vendor_Distribution_Poles_PoleId
					for xml path ('IMAGE'), type) as '*'
			from 
				tb_Vendor_Distribution_Poles as Pole
				LEFT JOIN tb_Settings as Href 
					ON Href.SettingId = 25
				LEFT JOIN tb_Settings as Src 
					ON Src.SettingId = 24
				LEFT JOIN tb_Utility_Polygons maps
					ON Pole.MapFk = maps.Utility_PolygonId 
				LEFT JOIN tb_Utility_Polygons sections
					ON Pole.SectionFk = sections.Utility_PolygonId 
				LEFT JOIN tb_XMissionStructures_PoleSpecies species
					ON Pole.SpecieFk = species.XMissionStructures_PoleSpeciesId 
				LEFT JOIN tb_Distribution_Classes classes
					ON Pole.ClassFk = classes.ClassId 
				LEFT JOIN tb_Distribution_Lengths lengths
					ON Pole.LengthFk = lengths.LengthId
				LEFT JOIN tb_XMissionStructures_PoleTreatments treatments
					ON Pole.TreatmentFk = treatments.XMissionStructures_PoleTreatmentId 				
			where
				Pole.Vendor_Distribution_Poles_PoleId = @InspectionVisitPoleId
				and Pole.DistributionType = 0
			for xml path ('VISIT')
	)		
	return @html

END

-- select convert(xml, dbo.fn_MapHTML_GpsVisitDistributionPole(3078))
 

